﻿
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

#region Clase

public class DataAccess {
    #region Variables
        //{Text, Numeric, DateTime, Bool}
        private Dictionary<string, string> dictionary = new Dictionary<string, string>();
        public enum DataAccessParameterType {Text, Numeric, DateTime, Bool}
        //private const int DataType_Text=1, DataType_Numeric=2, DataType_DateTime=3, DataType_Bool=4;
        private string connectionStrings = ConfigurationManager.ConnectionStrings["dbMain"].ConnectionString;
        private string query = "";
        private int timeout = 90;

        private DataTableReader recordset = null;
        public DataTableReader Recordset { get { return recordset; } set { recordset = value; }}
        
        private SqlConnection connection = new SqlConnection();
        public SqlConnection Connection { get { return connection; } set { connection = value; }}
        
    #endregion

    #region Builder

        /// <summary>
        /// Constructor
        /// </summary>
	    public DataAccess(){}

    #endregion

    #region Methods

        public void AddParam(string parameterName, DataAccessParameterType dataType, string value) {
            if (dataType == DataAccessParameterType.DateTime && value != "NULL"){value = "";}
			if (dataType == DataAccessParameterType.Numeric && value == null){value = "0";}
			if (dataType == DataAccessParameterType.Text){value = value.Replace("'", "''");}

            dictionary.Add(parameterName, value);

        }
        
        public DataSet ExecuteProcedure(string procedure){
			SqlCommand command = new SqlCommand();
		    DataSet ds = new DataSet();
			try {
				this.query = this.CreateQueryString(procedure);
                //OpenConnection();
                SqlDataAdapter SqlDA = new SqlDataAdapter();
                SqlDA.SelectCommand = command;
                SqlDA.SelectCommand.Connection = OpenConnection();
                SqlDA.SelectCommand.CommandType = CommandType.Text;
                SqlDA.SelectCommand.CommandText = this.query;
                SqlDA.SelectCommand.CommandTimeout = this.timeout;
                SqlDA.Fill(ds);

                Recordset = ds.Tables[0].CreateDataReader();

				try {command.Connection.Open();} catch {command.Connection.Close(); command.Connection.Open();}
				System.GC.Collect();
			
			} catch (System.Exception err){
				throw new Exception("ddevcom.ExecuteProcedureConsult " + err.Message + " " + query);
			}
            return ds;
		}


        public int ExecuteProcedureInsertOrUpdate(string procedure, string InsertOrUpdate){
			SqlCommand command = new SqlCommand();
            int scalar = 0;
			try {
				this.query = this.CreateQueryString(procedure);
                //OpenConnection();
                
                command.Connection = OpenConnection();
			    command.CommandType = CommandType.Text; 
			    command.CommandText = this.query;
			    command.CommandTimeout = this.timeout;
                scalar = (int)command.ExecuteScalar();

				try {command.Connection.Open();} catch {command.Connection.Close(); command.Connection.Open();}
				System.GC.Collect();
			
			} catch (System.Exception err){
				throw new Exception("ddevcom.ExecuteProcedureInsertOrUpdate " + err.Message + " " + query);
			}
            return scalar;
		}

    		

        private string CreateQueryString(string procedure) {
            string param = "";
            foreach( KeyValuePair<string, string> kvp in this.dictionary ){
                param = param + kvp.Key + "=" + kvp.Value + ", ";
            }
            if(param.Length > 0){param = param.Substring(0, param.Length-2);}

            return "EXEC " + procedure + " " + param;
        }


        /* Conection execute
         */
        public SqlConnection OpenConnection() {
            connection = new SqlConnection(connectionStrings);
            connection.Open();
            return connection;
        }

        /* Consult execute, recibe la consulta "sql"
         
        public DataSet ExecuteQuery(string sql)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = this.OpenConnection())
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                da.Fill(ds);
                con.Dispose();
            }
            return ds;
        }

        /* Consult execute, recibe la consulta "sql" y los indices indicadores
         * del tamaño del dataset
         
        public DataSet ExecuteQuery(string sql, int startRecord, int maxRecord)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = this.OpenConnection())
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                da.Fill(ds, startRecord, maxRecord, "Logs");
                con.Dispose();
            }
            return ds;
        }
        
        /* Transaction execute, especifica "yes" para recibir el ID del registro insertado,
         * especifica no para recibir la cantidad de filas afectadas
        
        public int ExecuteDML(string sql, string id)
        {
            using (SqlConnection con = this.OpenConnection())
            {
                SqlCommand cmd = new SqlCommand(sql, con);
                int aux = 0;
                if (id.Equals("yes"))
                {
                   aux = (int)cmd.ExecuteScalar();
                }
                else
                {
                    aux = cmd.ExecuteNonQuery();
                }                
                con.Dispose();
                return aux;
            }
        }*/

    #endregion
    
}

#endregion



